#!/use/bin/python
# coding=utf-8
# 主动服务 服务订阅数量 pymysql版  无需执行过一次
import datetime
import pymysql
from datetime import date, timedelta

from dbutils.pooled_db import PooledDB

# proactive_service_conf 数据源
def getConfConnection():
    # 开发环境
    #pool = PooledDB(pymysql, 1, host='172.20.135.96', user='pushdb', passwd='SkYWOrTh$TcOs',
    #                db='proactive_service_conf', port=3306)  # 1为连接池里的最少连接数
    # 测试环境
    pool = PooledDB(pymysql, 1, host='172.20.150.109', user='test_dmp', passwd='DghHC3lFM1KzT3ZJ',
                    db='proactive_service_conf', port=3307)  # 1为连接池里的最少连接数
    # pool = PooledDB(pymysql,1,host='127.0.0.1',user='root',passwd='root',db='life_assistant_data',port=3306) # 5为连接池里的最少连接数
    conn = pool.connection()
    cur = conn.cursor()
    return conn, cur

# proactive_service_data 数据源
def getDataConnection():
    # 开发环境
    #pool = PooledDB(pymysql, 1, host='172.20.151.80', user='pushdb', passwd='SkYWOrTh$TcOs',
    #                db='proactive_service_data',port=3306)  # 1为连接池里的最少连接数
    pool = PooledDB(pymysql, 1, host='172.20.154.103', user='test_dmp', passwd='DghHC3lFM1KzT3ZJ',
                    db='proactive_service_data', port=3407)  # 1为连接池里的最少连接数
    # pool = PooledDB(pymysql,1,host='127.0.0.1',user='root',passwd='root',db='life_assistant_data',port=3306) # 5为连接池里的最少连接数
    conn = pool.connection()
    cur = conn.cursor()
    return conn, cur

#根据传入的SQL 返回执行SQL返回的数量
def selectNumBySql(sql,conf=2):
    if conf == 1:
        conn, cur = getConfConnection()
    else:
        conn, cur = getDataConnection()
    #print(sql)
    cur.execute(sql)
    numResult = cur.fetchone()
    num = 0
    if numResult is not None:
        num = numResult[0]
    return num

# 记录 主动服务-各服务订阅用户数 analysis_subscribe_number
#date 当天 beforeDate 昨天 serId 服务 execute_flag 是否执行一次 0否 1是
def insertSubscribeNumber(date,beforeDate,serId,execute_flag):
    try:
        logStr = "无需执行一次"
        conn, cur = getDataConnection()
        #判断 analysis_subscribe_number 表是否已经存在当天 的数据
        countNum = selectNumBySql(
            f"select count(1) num from analysis_subscribe_number t where 1=1 and t.service_key ='{serId}' and t.ref_date =  '{date}' and t.execute_flag = {execute_flag}")

        indexArray = serIdToStateIndex(serId)
        # 获取当日订阅数量为累计订阅数
        selectSql = f"select count(1) subNum from life_assistant_devices t where (switches_state1 & (1 << ({indexArray[0]} - 1)))<>0 and (switches_state1 & (1 << ({indexArray[1]} - 1)))<>0"
        if execute_flag ==1:
            selectSql = f"{selectSql} and (switches_state1 & (1 << ({indexArray[2]} - 1)))<>0"
            logStr = "执行一次"
        cumulateNum = selectNumBySql(selectSql, 1)
        #计算与昨天的订阅量差值 作为新增的数量
        resultBeforeNum = selectNumBySql(
            f"select cumulate_num from analysis_subscribe_number t where t.service_key ='{serId}' and t.ref_date =  '{beforeDate}' and t.execute_flag = {execute_flag}")
        newNum = cumulateNum - resultBeforeNum
        serName = serIdToName(serId)

        if countNum == 0:
            logDetail = f"{logStr}:{date}日新增-订阅{serName}设备量累积数为{cumulateNum},本日新增为{newNum}"
            print(logDetail)
            # 插入
            insertSql = f"""insert into analysis_subscribe_number(new_num,cumulate_num,ref_date,service_key,execute_flag) 
                value({newNum},{cumulateNum},'{date}','{serId}',{execute_flag})"""
            cur.execute(insertSql)
        else:
            logDetail = f"{logStr}:{date}日更新-订阅{serName}设备量累积数为{cumulateNum},本日新增为{newNum}"
            print(logDetail)
            # 更新
            updateSql = f"""
                update analysis_subscribe_number set new_num = {newNum} ,cumulate_num = {cumulateNum} ,update_time = now()  where
                    ref_date = '{date}' and service_key ='{serId}' and execute_flag = {execute_flag} 
            """
            cur.execute(updateSql)
        saveLog(logDetail, date)
        conn.commit()
    except Exception as e:
        print(e)
    finally:
        cur.close()
        conn.close()

# 记录 主动服务-助眠功能订阅用户数 analysis_subscribe_number
def insertZmgnSubscribeNumber(date,beforeDate):
    try:
        conn, cur = getDataConnection()
        #判断 analysis_subscribe_number 表是否已经存在当天 的数据
        countNum = selectNumBySql(
            f"select count(1) num from analysis_subscribe_number t where 1=1 and t.service_key ='zmgn' and t.ref_date =  '{date}' ")
        # 获取当日订阅数量为累计订阅数
        selectSql = f"select count(1) subNum from life_assistant_devices t where (switches_state1 & (1 << (16 - 1)))<>0"
        cumulateNum = selectNumBySql(selectSql, 1)
        #计算与昨天的订阅量差值 作为新增的数量
        resultBeforeNum = selectNumBySql(
            f"select cumulate_num from analysis_subscribe_number t where t.service_key ='zmgn' and t.ref_date =  '{beforeDate}' ")
        newNum = cumulateNum - resultBeforeNum
        if countNum == 0:
            logDetail = f"{date}日新增-订阅助眠功能设备量累积数为{cumulateNum},本日新增为{newNum}"
            print(logDetail)
            # 插入
            insertSql = f"""insert into analysis_subscribe_number(new_num,cumulate_num,ref_date,service_key) 
                value({newNum},{cumulateNum},'{date}','zmgn')"""
            cur.execute(insertSql)
        else:
            logDetail = f"{date}日更新-订阅助眠功能设备量累积数为{cumulateNum},本日新增为{newNum}"
            print(logDetail)
            # 更新
            updateSql = f"""
                update analysis_subscribe_number set new_num = {newNum} ,cumulate_num = {cumulateNum} ,update_time = now()  where
                    ref_date = '{date}' and service_key ='zmgn' 
            """
            cur.execute(updateSql)
        saveLog(logDetail, date)
        conn.commit()
    except Exception as e:
        print(e)
    finally:
        cur.close()
        conn.close()


# 记录 主动服务-3个服务任意服务(总体)订阅用户数 analysis_subscribe_number
def insertAllSubscribeNumber(date,beforeDate,execute_flag):
    try:
        logStr = "无需执行一次"
        conn, cur = getDataConnection()
        # 判断 analysis_subscribe_number 表是否已经存在当天 的数据
        countNum = selectNumBySql(
            f"select count(1) num from analysis_subscribe_number t where 1=1 and t.service_key ='all' and t.ref_date =  '{date}' and t.execute_flag = {execute_flag}")
        # 获取当日订阅数量为累计订阅数
        selectSql = f"""select count(1) subNum from 	life_assistant_devices t where ( (switches_state1 & (1 << (1 - 1)))<>0 and 	(switches_state1 & (1 << (2 - 1)))<>0	)
        							or((switches_state1 & (1 << (3 - 1)))<>0 and 	(switches_state1 & (1 << (4 - 1)))<>0)
        							or((switches_state1 & (1 << (5 - 1)))<>0 and 	(switches_state1 & (1 << (6 - 1)))<>0)
        							or((switches_state1 & (1 << (7 - 1)))<>0 and 	(switches_state1 & (1 << (8 - 1)))<>0)
        							or((switches_state1 & (1 << (9 - 1)))<>0 and 	(switches_state1 & (1 << (10 - 1)))<>0)"""
        if execute_flag == 1:
            selectSql = f"""select count(1) subNum from 	life_assistant_devices t where ( (switches_state1 & (1 << (1 - 1)))<>0 and 	(switches_state1 & (1 << (2 - 1)))<>0 and (switches_state1 & (1 << (11 - 1)))<>0	)
            							or((switches_state1 & (1 << (3 - 1)))<>0 and 	(switches_state1 & (1 << (4 - 1)))<>0 and (switches_state1 & (1 << (12 - 1)))<>0)
            							or((switches_state1 & (1 << (5 - 1)))<>0 and 	(switches_state1 & (1 << (6 - 1)))<>0 and (switches_state1 & (1 << (13 - 1)))<>0)
            							or((switches_state1 & (1 << (7 - 1)))<>0 and 	(switches_state1 & (1 << (8 - 1)))<>0 and (switches_state1 & (1 << (14 - 1)))<>0)
            							or((switches_state1 & (1 << (9 - 1)))<>0 and 	(switches_state1 & (1 << (10 - 1)))<>0 and (switches_state1 & (1 << (15 - 1)))<>0)"""
            logStr = "执行一次"
        cumulateNum = selectNumBySql(selectSql, 1)
        # 计算与昨天的订阅量差值 作为新增的数量
        resultBeforeNum = selectNumBySql(
            f"select cumulate_num from analysis_subscribe_number t where t.service_key ='all' and t.ref_date =  '{beforeDate}' and t.execute_flag = {execute_flag}")
        newNum = cumulateNum - resultBeforeNum

        if countNum == 0:
            logDetail = f"{logStr}:{date}日新增-订阅生活助手设备量累积数为{cumulateNum},本日新增为{newNum}"
            print(logDetail)
            # 插入
            insertSql = f"""insert into analysis_subscribe_number(new_num,cumulate_num,ref_date,service_key,execute_flag) 
                value({newNum},{cumulateNum},'{date}','all',{execute_flag})"""
            cur.execute(insertSql)
        else:
            logDetail = f"{logStr}:{date}日更新-订阅生活助手设备量累积数为{cumulateNum},本日新增为{newNum}"
            print(logDetail)
            # 更新
            updateSql = f"""
                update analysis_subscribe_number set new_num = {newNum} ,cumulate_num = {cumulateNum} ,update_time = now()  where
                    ref_date = '{date}' and service_key ='all' and execute_flag = {execute_flag}
            """
            cur.execute(updateSql)
        saveLog(logDetail, date)
        conn.commit()
    except Exception as e:
        print(e)
    finally:
        cur.close()
        conn.close()

# 记录 当月 主动服务-各服务订阅用户数 analysis_subscribe_number
def insertSubscribeNumberMonth(toMonthDay,serId,execute_flag):
    try:
        logStr = "无需执行一次"
        conn, cur = getDataConnection()
        # 判断 analysis_subscribe_number 表是否已经存在当天 的数据
        countNum = selectNumBySql(
            f"select count(1) num from analysis_subscribe_number t where 1=1 and t.service_key ='{serId}' and t.ref_date =  '{toMonthDay}' and t.execute_flag = {execute_flag}")
        indexArray = serIdToStateIndex(serId)
        # 获取当日订阅数量为累计订阅数
        selectSql = f"select count(1) subNum from life_assistant_devices t where (switches_state1 & (1 << ({indexArray[0]} - 1)))<>0 and (switches_state1 & (1 << ({indexArray[1]} - 1)))<>0"
        if execute_flag == 1:
            selectSql = f"{selectSql} and (switches_state1 & (1 << ({indexArray[2]} - 1)))<>0"
            logStr = "执行一次"
        cumulateNum = selectNumBySql(selectSql, 1)
        # 计算当月新增订阅数
        newNum = selectNumBySql(
            f"select sum(new_num) from analysis_subscribe_number t  where SUBSTR( t.ref_date, 1,7) = '{toMonthDay}' and LENGTH(t.ref_date) = 10   and service_key ='{serId}' and execute_flag = {execute_flag}")
        serName = serIdToName(serId)

        if countNum == 0:
            logDetail = f"{logStr}:{toMonthDay}月新增-订阅{serName}设备量累积数为{cumulateNum},本月新增为{newNum}"
            print(logDetail)
            # 插入
            insertSql = f"""insert into analysis_subscribe_number(new_num,cumulate_num,ref_date,service_key,execute_flag) 
                value({newNum},{cumulateNum},'{toMonthDay}','{serId}',{execute_flag})"""
            cur.execute(insertSql)
        else:
            logDetail = f"{logStr}:{toMonthDay}月更新-订阅{serName}设备量累积数为{cumulateNum},本月新增为{newNum}"
            print(logDetail)
            # 更新
            updateSql = f"""
                update analysis_subscribe_number set new_num = {newNum} ,cumulate_num = {cumulateNum} ,update_time = now()  where
                    ref_date = '{toMonthDay}' and service_key ='{serId}' and execute_flag = {execute_flag} 
            """
            cur.execute(updateSql)
        saveLog(logDetail, toMonthDay)
        conn.commit()
    except Exception as e:
        print(e)
    finally:
        cur.close()
        conn.close()


# 记录 当月 主动服务-助眠功能订阅用户数 analysis_subscribe_number
def insertZmgnSubscribeNumberMonth(toMonthDay):
    try:
        conn, cur = getDataConnection()
        # 判断 analysis_subscribe_number 表是否已经存在当天 的数据
        countNum = selectNumBySql(
            f"select count(1) num from analysis_subscribe_number t where 1=1 and t.service_key ='zmgn' and t.ref_date =  '{toMonthDay}' ")
        # 获取累计订阅数
        selectSql = f"select count(1) subNum from life_assistant_devices t where (switches_state1 & (1 << (16 - 1)))<>0"
        cumulateNum = selectNumBySql(selectSql, 1)
        # 计算当月新增订阅数
        newNum = selectNumBySql(
            f"select sum(new_num) from analysis_subscribe_number t  where SUBSTR( t.ref_date, 1,7) = '{toMonthDay}' and LENGTH(t.ref_date) = 10   and service_key ='zmgn' ")

        if countNum == 0:
            logDetail = f"{toMonthDay}月新增-订阅助眠功能设备量累积数为{cumulateNum},本日新增为{newNum}"
            print(logDetail)
            # 插入
            insertSql = f"""insert into analysis_subscribe_number(new_num,cumulate_num,ref_date,service_key) 
                value({newNum},{cumulateNum},'{toMonthDay}','zmgn')"""
            cur.execute(insertSql)
        else:
            logDetail = f"{toMonthDay}月更新-订阅助眠功能设备量累积数为{cumulateNum},本日新增为{newNum}"
            print(logDetail)
            # 更新
            updateSql = f"""
                update analysis_subscribe_number set new_num = {newNum} ,cumulate_num = {cumulateNum} ,update_time = now()  where
                    ref_date = '{toMonthDay}' and service_key ='zmgn' 
            """
            cur.execute(updateSql)
        saveLog(logDetail, toMonthDay)
        conn.commit()
    except Exception as e:
        print(e)
    finally:
        cur.close()
        conn.close()

# 记录 当月 主动服务--3个服务任意服务(总体)订阅用户数 analysis_subscribe_number
def insertAllSubscribeNumberMonth(toMonthDay,execute_flag):
    try:
        logStr = "无需执行一次"
        conn, cur = getDataConnection()
        # 判断 analysis_subscribe_number 表是否已经存在当天 的数据
        countNum = selectNumBySql(
            f"select count(1) num from analysis_subscribe_number t where 1=1 and t.service_key ='all' and t.ref_date =  '{toMonthDay}' and t.execute_flag = {execute_flag}")
        # 获取当日订阅数量为累计订阅数
        selectSql = f"""select count(1) subNum from 	life_assistant_devices t where ( (switches_state1 & (1 << (1 - 1)))<>0 and 	(switches_state1 & (1 << (2 - 1)))<>0	)
                							or((switches_state1 & (1 << (3 - 1)))<>0 and 	(switches_state1 & (1 << (4 - 1)))<>0)
                							or((switches_state1 & (1 << (5 - 1)))<>0 and 	(switches_state1 & (1 << (6 - 1)))<>0)
                							or((switches_state1 & (1 << (7 - 1)))<>0 and 	(switches_state1 & (1 << (8 - 1)))<>0)
                							or((switches_state1 & (1 << (9 - 1)))<>0 and 	(switches_state1 & (1 << (10 - 1)))<>0)
                                            or((switches_state1 & (1 << (19 - 1)))<>0 and 	(switches_state1 & (1 << (20 - 1)))<>0)"""
        if execute_flag == 1:
            selectSql = f"""select count(1) subNum from 	life_assistant_devices t where ( (switches_state1 & (1 << (1 - 1)))<>0 and 	(switches_state1 & (1 << (2 - 1)))<>0 and (switches_state1 & (1 << (11 - 1)))<>0	)
                    							or((switches_state1 & (1 << (3 - 1)))<>0 and 	(switches_state1 & (1 << (4 - 1)))<>0 and (switches_state1 & (1 << (12 - 1)))<>0)
                    							or((switches_state1 & (1 << (5 - 1)))<>0 and 	(switches_state1 & (1 << (6 - 1)))<>0 and (switches_state1 & (1 << (13 - 1)))<>0)
                    							or((switches_state1 & (1 << (7 - 1)))<>0 and 	(switches_state1 & (1 << (8 - 1)))<>0 and (switches_state1 & (1 << (14 - 1)))<>0)
                    							or((switches_state1 & (1 << (9 - 1)))<>0 and 	(switches_state1 & (1 << (10 - 1)))<>0 and (switches_state1 & (1 << (15 - 1)))<>0)
                                                or((switches_state1 & (1 << (19 - 1)))<>0 and 	(switches_state1 & (1 << (20 - 1)))<>0)"""
            logStr = "执行一次"
        cumulateNum = selectNumBySql(selectSql, 1)
        # 计算当月新增订阅数
        newNum = selectNumBySql(
            f"select sum(new_num) from analysis_subscribe_number t  where SUBSTR( t.ref_date, 1,7) = '{toMonthDay}' and LENGTH(t.ref_date) = 10   and service_key ='all' and execute_flag = {execute_flag}")
        if countNum == 0:
            logDetail = f"{logStr}:{toMonthDay}月新增-总体订阅设备量累积数为{cumulateNum},本日新增为{newNum}"
            print(logDetail)
            # 插入
            insertSql = f"""insert into analysis_subscribe_number(new_num,cumulate_num,ref_date,service_key,execute_flag) 
                value({newNum},{cumulateNum},'{toMonthDay}','all',{execute_flag})"""
            cur.execute(insertSql)
        else:
            logDetail = f"{logStr}:{toMonthDay}月更新-总体订阅设备量累积数为{cumulateNum},本日新增为{newNum}"
            print(logDetail)
            # 更新
            updateSql = f"""
                update analysis_subscribe_number set new_num = {newNum} ,cumulate_num = {cumulateNum} ,update_time = now()  where
                    ref_date = '{toMonthDay}' and service_key ='all' and execute_flag = {execute_flag} 
            """
            cur.execute(updateSql)
        saveLog(logDetail, toMonthDay)
        conn.commit()
    except Exception as e:
        print(e)
    finally:
        cur.close()
        conn.close()

#根据serId 获取服务中文名
def serIdToName(serId):
    if serId =='zjkj':
        serName = "早间开机"
    elif serId =='jkzx':
        serName = "健康作息"
    elif serId =='yjyl':
        serName = "夜间音量"
    elif serId =='zjtx':
        serName = "追剧提醒"
    elif serId =='sjtx':
        serName = "睡觉提醒"
    elif serId =='zmgn':
        serName = "助眠功能"
    elif serId =='jykj':
        serName = "静音开机"
    else:
        serName = "其他"
    return serName

#根据serId 获取开关位置
#返回: 第一位UI开关 第二位UI服务开关  第三位 执行标志位
def serIdToStateIndex(serId):
    indexArray = []
    if serId =='zjkj':
        indexArray.append(1)
        indexArray.append(2)
        indexArray.append(11)
    elif serId =='yjyl':
        indexArray.append(3)
        indexArray.append(4)
        indexArray.append(12)
    elif serId =='jkzx':
        indexArray.append(5)
        indexArray.append(6)
        indexArray.append(13)
    elif serId =='zjtx':
        indexArray.append(7)
        indexArray.append(8)
        indexArray.append(14)
    elif serId =='sjtx':
        indexArray.append(9)
        indexArray.append(10)
        indexArray.append(15)
    elif serId =='jykj':
        indexArray.append(19)
        indexArray.append(20)
    return indexArray


# 获取当前日期
def todayYMD():
    today = datetime.datetime.now()-1
    # 获取想要的日期的时间
    re_date = (today).strftime('%Y-%m-%d')
    return re_date


# 获取前1天或N天的日期，beforeOfDay=1：前1天；beforeOfDay=N：前N天
def getdate(beforeOfDay):
    today = datetime.datetime.now()
    # 计算偏移量
    offset = datetime.timedelta(days=-beforeOfDay)
    # 获取想要的日期的时间
    re_date = (today + offset).strftime('%Y-%m-%d')
    return re_date


# 获取前1天或N天的日期，beforeOfDay=1：前1天；beforeOfDay=N：前N天
def getMonthdate(beforeOfDay):
    today = datetime.datetime.now()
    # 计算偏移量
    offset = datetime.timedelta(days=-beforeOfDay)
    # 获取想要的日期的时间
    re_date = (today + offset).strftime('%Y-%m')
    return re_date

#保存日志
def saveLog(detail,ref_date):
    try:
        conn, cur = getDataConnection()
        jobId = 39
        jobName = '核心指标-生活助手-订阅用户数(无需执行一次)'
        tableName = 'analysis_subscribe_number'
        # 插入日志表
        saveLogSql = f"""insert into log_xxljob_analysis (job_id,job_name,detail,table_name,ref_date) 
                                value({jobId},'{jobName}','{detail}','{tableName}','{ref_date}')"""
        cur.execute(saveLogSql)
        conn.commit()
    except Exception as e:
        print(e)
    finally:
        cur.close()
        conn.close()

# 执行各服务每日订阅次数  execute_flag : 是否执行一次 1是 0 否
def serviceSubscribeNumber(execute_flag):
    insertSubscribeNumber(date, beforeDate, 'zjkj', execute_flag)
    insertSubscribeNumber(date, beforeDate, 'jkzx', execute_flag)
    insertSubscribeNumber(date, beforeDate, 'yjyl', execute_flag)
    insertSubscribeNumber(date, beforeDate, 'zjtx', execute_flag)
    insertSubscribeNumber(date, beforeDate, 'sjtx', execute_flag)
    insertAllSubscribeNumber(date, beforeDate,execute_flag)

# 执行各服务每月订阅次数  execute_flag : 是否执行一次 1是 0 否
def serviceSubscribeNumberMonth(execute_flag):
    insertSubscribeNumberMonth(monthdate, 'zjkj', execute_flag)
    insertSubscribeNumberMonth(monthdate, 'jkzx', execute_flag)
    insertSubscribeNumberMonth(monthdate, 'yjyl', execute_flag)
    insertSubscribeNumberMonth(monthdate, 'zjtx', execute_flag)
    insertSubscribeNumberMonth(monthdate, 'sjtx', execute_flag)
    insertAllSubscribeNumberMonth(monthdate,execute_flag)

if __name__ == '__main__':
    #today = todayYMD()
    date = getdate(1)
    beforeDate = getdate(2)
    monthdate = getMonthdate(1)
    #today = "2021-12-13"
    print (f"{date} 日期,执行<主动服务订阅用户数>报表更新报表")
    serviceSubscribeNumber(0)
    #serviceSubscribeNumberMonth(0)
    #serviceSubscribeNumber(1)
    #serviceSubscribeNumberMonth(1)
    insertSubscribeNumber(date, beforeDate, 'jykj',0 )
    insertSubscribeNumberMonth(monthdate, 'jykj',0 )
    insertZmgnSubscribeNumber(date,beforeDate)
    insertZmgnSubscribeNumberMonth(monthdate)
